<?php
	function f280($sql){
		//data handle instance
		//set request
		//define format
		//set format
		//define query string
		//process time
		//process fitler
		$dataHandle = new DataHandle();
		$dataHandle->setRequest("280");

		$dataType = getValue($_GET['dataType']);
		$action = getValue($_GET['action']);

		if ($dataType == "json"){
			$error = $dataHandle->processTime($startTime, $endTime);
			if($error == ""){
				$filter = $dataHandle->processFilters($startTime, $endTime, "lin_produced_view","start_time");
				$query = getJSONQuery($startTime, $endTime, $filter);
				print($dataHandle->printJSON($sql, $query));
			}
			else
				print($error);
		}
		else if($action == "getYears"){
			$query = getYears();
			print($dataHandle->printJSON($sql, $query));
		}
		else if($action == "getLines"){
			$query = getLines();
			print($dataHandle->printJSON($sql, $query));
		}
		else{
			$temp = array();
			$temp['error'] = "280: Bad request";
			print(json_encode($temp));
		}

		unset($dataHandle);
	}

	function getJSONQuery(&$startTime, &$endTime, &$filter){

		$queryString = "
			SELECT     
			  line_id as line,
			  shift,
			  speed,
			  minutes,
			  product_id as item,
			  description, 

			  CASE 
			    WHEN roll_code_bin_change = 0 
			      THEN 0 
			    ELSE CAST(
			      ROUND(
			        100 * 
			        roll_code_bin_change 
			        / 
			        (
			          casepacker 
			          + check_weighter 
			          + cartoner_wrapper 
			          + filling_machine
			          + isnull(unaccounted, 0) 
			          + no_mix_no_materials 
			          + roll_code_bin_change
			        )
			      , 1) 
			    AS numeric(9)
			    ) END AS rollCodePerc,

			  CASE 
			    WHEN no_mix_no_materials = 0 
			      THEN 0 
			    ELSE CAST(
			      ROUND(
			        100 * 
			        no_mix_no_materials 
			        / (
			          casepacker 
			          + check_weighter 
			          + cartoner_wrapper 
			          + filling_machine
			          + isnull(unaccounted, 0) 
			          + no_mix_no_materials 
			          + roll_code_bin_change
			        )
			      , 1) 
			    AS numeric(9)
			    ) END AS noMixPerc, 

			  CASE 
			    WHEN unaccounted = 0 
			      THEN 0 
			    ELSE CAST(
			      ROUND(
			      100 
			      * unaccounted 
			      / (
			          casepacker 
			          + check_weighter 
			          + cartoner_wrapper 
			          + filling_machine 
			          + isnull(unaccounted,0) 
			          + no_mix_no_materials 
			          + roll_code_bin_change
			        )
			      , 1) 
			    AS numeric(9)
			    ) END AS unAccPerc, 

			  CASE 
			    WHEN filling_machine = 0 
			      THEN 0 
			    ELSE CAST(
			      ROUND(
			        100 
			        * filling_machine 
			        / (
			            casepacker 
			            + check_weighter 
			            + cartoner_wrapper 
			            + filling_machine
			            + isnull(unaccounted, 0) 
			            + no_mix_no_materials 
			            + roll_code_bin_change
			          )
			        , 1) 
			      AS numeric(9)
			      ) END AS fillMachinePerc, 

			  CASE 
			    WHEN cartoner_wrapper = 0 
			      THEN 0 
			    ELSE CAST(
			      ROUND(
			        100 
			        * cartoner_wrapper 
			        / (
			              casepacker 
			            + check_weighter 
			            + cartoner_wrapper 
			            + filling_machine
			            + isnull(unaccounted, 0) 
			            + no_mix_no_materials 
			            + roll_code_bin_change
			          )
			        , 1) 
			      AS numeric(9)
			      ) END AS cartWrappPerc, 

			  CASE 
			    WHEN check_weighter = 0 
			      THEN 0 
			    ELSE CAST(
			      ROUND(
			        100 
			        * check_weighter 
			        / (
			              casepacker 
			            + check_weighter 
			            + cartoner_wrapper 
			            + filling_machine
			            + isnull(unaccounted, 0) 
			            + no_mix_no_materials 
			            + roll_code_bin_change
			          )
			        , 1) 
			      AS numeric(9)
			      ) END AS checkWeighterPerc, 

			  CASE 
			    WHEN casepacker = 0 
			      THEN 0 
			    ELSE CAST(
			      ROUND(
			        100 
			        * casepacker 
			        / (
			            casepacker 
			            + check_weighter 
			            + cartoner_wrapper 
			            + filling_machine 
			            + isnull(unaccounted,0)
			            + no_mix_no_materials 
			            + roll_code_bin_change
			          )
			        , 1) 
			      AS numeric(9)
			      ) END AS casePackerPerc, 

			  CAST(
			    ROUND(
			          casepacker 
			          + check_weighter 
			          + cartoner_wrapper 
			          + filling_machine 
			          + ISNULL(unaccounted, 0) 
			          + no_mix_no_materials 
			          + roll_code_bin_change
			        , 1) 
			  AS numeric(9, 1)
			  ) AS totalDowntime, 

			  CAST(
			    ROUND(
			      100 * 
			      (
			        casepacker 
			        + check_weighter 
			        + cartoner_wrapper 
			        + filling_machine 
			        + ISNULL(unaccounted, 0) 
			        + no_mix_no_materials 
			        + roll_code_bin_change
			      ) 
			      / minutes
			    , 1) 
			    AS numeric(8, 1)
			  ) AS totalDowntimePerc

			FROM  lin_produced_view";

		$queryString = $queryString.$filter;

		$queryString = $queryString."order by end_time desc;";

		return $queryString;
	}

      
	function getYears(){
		$queryString = 
			"select distinct 
				datepart (year, modified_date) as availableYear 
			from lin_produced_view 
			order by availableYear desc";
		return $queryString;
	}
	function getLines(){
		$queryString = 
			"select distinct 
				line_id as availableLine 
			from lin_produced_view 
			order by availableLine asc";
		return $queryString;
	}
?>